if (object_id ('Compra') is not null)
	drop procedure Compra
go
CREATE PROCEDURE Compra
	 @IdProd INT
	,@Cantidad INT
	,@IdCust NVARCHAR(100)
	,@Restante INT OUTPUT    
AS				

	DECLARE @Precio DECIMAL
		   ,@Stock INT
		   ,@Estado INT
	
		SELECT @Precio = UnitPrice, @Stock = UnitsInStock
			FROM Products
			WHERE ProductID = @IdProd
		
		BEGIN TRY
			BEGIN TRANSACTION
			--print @Precio
			INSERT INTO [Orders] (CustomerID, EmployeeID, OrderDate) values (@IdCust, 4, CURRENT_TIMESTAMP)
			INSERT INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity) VALUES (IDENT_CURRENT('Orders'), @IdProd, @Precio, @Cantidad)
			SET @Restante = @Stock - @Cantidad	
			print @stock
			--print @cantidad
			COMMIT TRAN
		END TRY
		
		BEGIN CATCH

			SELECT @@ERROR
			PRINT ERROR_MESSAGE()
			ROLLBACK TRANSACTION

		END CATCH

GO 


declare @salida nvarchar(100)
exec Compra @Restante = @salida output
             , @idProd = 17
			 , @Cantidad = 12
			 , @IdCust = 'ERNSH'
			  
 SELECT @salida
